package com.jf.cloud.service.sysmanager.handler;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Pattern;

import javax.servlet.http.HttpServletRequest;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.stereotype.Component;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.MultipartHttpServletRequest;

import com.jf.cloud.service.sysmanager.common.db.BaseDao;
import com.jf.cloud.service.sysmanager.common.util.DateUtil;
import com.jf.cloud.service.sysmanager.common.util.ObjectUtil;
import com.jf.cloud.service.sysmanager.common.util.RegexValidUtil;
import com.jf.cloud.service.sysmanager.common.util.StringUtil;
import com.jf.cloud.service.sysmanager.common.util.XSSFExcelUtil;
import com.jf.cloud.service.sysmanager.entity.CardUser;
import com.jf.cloud.service.sysmanager.support.entity.Result;

/**
 * 导入Excel辅助
 * 
 * @author 朱建文 创建时间：2018年6月6日 下午3:25:59
 */
@Component
public class ExcelHandler {

    /**
     * 日志
     */
    private static Log log = LogFactory.getLog(ExcelHandler.class);
    
    /**
     * JDBC 模板
     */
    @Autowired
    private BaseDao baseDao;
    
	/**
	 * 导入点卡帐号Excel
	 * 
	 * @author 朱建文 创建时间：2018年6月6日 下午3:28:11
	 */
	public Result<List<CardUser>> ImportCardUserExcel(HttpServletRequest request){
		Result<List<CardUser>> result = new Result<List<CardUser>>();
		result.setCode(-1);
		result.setMessage("处理Excel出错！");
		try {
			MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
			MultipartFile file = multipartRequest.getFile("importExcel");
			
			if (file.getSize() == 0) {
				return result;
			}
			
			String origianlFileName = file.getOriginalFilename();
			
			Pattern pattern = Pattern.compile(".+(.xls|.xlsx)$");
			if (StringUtil.isNull(origianlFileName) || !pattern.matcher(origianlFileName).matches()) {
				result.setMessage("请检查文件格式！");
				return result;
			}
			
			Map<String, Integer> platformMap = getPlatformData();
			
			XSSFWorkbook workBook = XSSFExcelUtil.read(file.getInputStream());
			XSSFSheet sheet = workBook.getSheetAt(0); // 读取工作表的数据 
			int startRow = 2; // 循环行Row  从第2行开始

			//导入记录集合
			List<CardUser> list = new ArrayList<CardUser>();
			CardUser cardUser = null;
			
			for (int rowIndex = startRow; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
				XSSFRow row = sheet.getRow(rowIndex);
				
				// 如果是空行 不处理
				if (row == null) { 
					continue;
				}
				
				cardUser = new CardUser();
				
				// 平台
				String platformName = XSSFExcelUtil.getCellValue(sheet, rowIndex, 1);
				if (StringUtil.isNull(platformName)) {
					result.setMessage("第" + (rowIndex + 1) + "行：平台为必填项！");
					return result;
				}
				platformName = platformName.trim();
				
				if (platformMap.get(platformName) == null) {
					result.setMessage("第" + (rowIndex + 1) + "行：平台不存在！");
					return result;
				}
				cardUser.setPlatformType(platformMap.get(platformName));
				
				// 帐号
				String userName = XSSFExcelUtil.getCellValue(sheet, rowIndex, 2);
				if (userName == null || userName.length() == 0) {
					result.setMessage("第" + (rowIndex + 1) + "行：帐号为必填项！");
					return result;
				}
				userName = userName.trim();
				cardUser.setUserName(userName);
				
				// 需要导入的账号数据在数据库已存在
				if (this.checkIsExist(cardUser.getPlatformType(), cardUser.getUserName())) {
					result.setMessage("第" + (rowIndex + 1) + "行：【" + platformName + "-" + userName + "】帐号已存在！");
					return result;
				}
				
				// 登录密码
				String loginPwd = XSSFExcelUtil.getCellValue(sheet, rowIndex, 6);
				if (StringUtil.isNull(loginPwd)) {
					result.setMessage("第" + (rowIndex + 1) + "行：登录密码为必填项！");
					return result;
				}
				loginPwd = loginPwd.trim();
				cardUser.setLoginPwd(loginPwd);
				
				// 支付密码
				String payPwd = XSSFExcelUtil.getCellValue(sheet, rowIndex, 7);
				if (StringUtil.isNull(payPwd)) {
					result.setMessage("第" + (rowIndex + 1) + "行：支付密码为必填项！");
					return result;
				}
				payPwd = payPwd.trim();
				cardUser.setPayPwd(payPwd);
				
				String userKey = XSSFExcelUtil.getCellValue(sheet, rowIndex, 3);
				String userEmail = XSSFExcelUtil.getCellValue(sheet, rowIndex, 4);
				String emailPwd = XSSFExcelUtil.getCellValue(sheet, rowIndex, 5);
				
				cardUser.setUserKey(ObjectUtil.convert(userKey, ""));
				cardUser.setUserEmail(ObjectUtil.convert(userEmail, ""));
				if (RegexValidUtil.compile(RegexValidUtil.email, userName) && StringUtil.isNull(userEmail)) {
					cardUser.setUserEmail(userName);
				}
				cardUser.setEmailPwd(ObjectUtil.convert(emailPwd, ""));
				
				cardUser.setUserEnable(0);
				cardUser.setUserStatus(0);
				cardUser.setCreateTime(DateUtil.getCurrentDateTime());
				
				list.add(cardUser);
				log.info("正在导入第" + (rowIndex + 1) + "行！");
			}
			result.setCode(0);
			result.setData(list);
			result.setMessage("处理Excel完毕！");
		} catch (Exception e) {
			log.error("处理Excel出错！");
		}
		return result;
	}
	
	/**
	 * 检查平台-帐号是否存在
	 * 
	 * @author 朱建文 创建时间：2018年6月6日 下午3:51:11
	 */
	private boolean checkIsExist(Integer platformId, String accountName){
		String sql = "SELECT COUNT(0) FROM card_base_user WHERE PLATFORM_TYPE = ? AND USER_NAME = ? ";
		return this.baseDao.find(sql, Integer.class, platformId, accountName) >= 1;
	}
	
	/**
	 * 获取平台-平台ID
	 * 
	 * @author 朱建文 创建时间：2018年6月6日 下午4:11:11
	 */
	public Map<String, Integer> getPlatformData() {
		Map<String, Integer> dataMap = new HashMap<String, Integer>();
		try {
			String sql = "SELECT ROW_ID value, TYPE_NAME label FROM pubg_base_type WHERE PARENT_ID = 20";
			List<Map<String, Object>> datalList = this.baseDao.findMapList(sql);
			for (Map<String, Object> map : datalList) {
				dataMap.put(map.get("label") + "", Integer.valueOf(map.get("value") + ""));
			}
		} catch (DataAccessException e) {
			log.error("获取平台-平台ID出错！", e);
		}
		return dataMap;
	}
}
